﻿
using bpms.Models;
using MySql.Data.MySqlClient;

namespace bpms.Dao
{
    /// <summary>
    /// 商品数据持久层
    /// </summary>
    public class ProductDao
    {
        /// <summary>
        /// 根据商品编号或者名称查询饮品列表
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public List<Product> GetList(string key)
        {
            var sql = "select * from product where 1=1 ";
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and (product_no like CONCAT('%', @key, '%') or name like CONCAT('%', @key, '%'))";
            }
            sql+= " order by id desc";
            return MySqlDbHelper.QueryList<Product>(sql, new MySqlParameter("@key", key));
        }

        /// <summary>
        /// 添加饮品
        /// </summary>
        /// <param name="product"></param>
        /// <returns></returns>
        public int Add(Product product)
        {
            var sql = $@"
                    INSERT INTO product
                    (product_no, name, stock, price, limit_stock, supplier, image, produce_date, quality_period, status, created_time, created_user)
                    values(@product_no, @name, @stock, @price, @limit_stock, @supplier, @image, @produce_date, @quality_period, @status, now(), @created_user)
                    ";
            var parameters = new[] {
                new MySqlParameter("@product_no", product.ProductNo),
                new MySqlParameter("@name", product.Name),
                new MySqlParameter("@price", product.Price),
                new MySqlParameter("@stock", product.Stock),
                new MySqlParameter("@Status", product.Status),
                new MySqlParameter("@limit_stock", product.LimitStock),
                new MySqlParameter("@supplier", product.Supplier),
                new MySqlParameter("@image", product.Image),
                new MySqlParameter("@produce_date", product.ProduceDate),
                new MySqlParameter("@quality_period", product.QualityPeriod),
                new MySqlParameter("@created_user", product.CreatedUser)
            };
            return MySqlDbHelper.Execute(sql, parameters);
        }

        /// <summary>
        /// 修改饮品
        /// </summary>
        /// <param name="product"></param>
        /// <returns></returns>
        public int Update(Product product) { 
         var sql= $@"
                UPDATE product
                SET product_no=@product_no, 
                name=@name, 
                stock=@stock, 
                price=@price, 
                limit_stock=@limit_stock, 
                supplier=@supplier, 
                image=@image, 
                produce_date=@produce_date, 
                quality_period=@quality_period, 
                status=@status
                WHERE id=@id;";
            var parameters = new[] {
                new MySqlParameter("@product_no", product.ProductNo),
                new MySqlParameter("@name", product.Name),
                new MySqlParameter("@price", product.Price),
                new MySqlParameter("@stock", product.Stock),
                new MySqlParameter("@Status", product.Status),
                new MySqlParameter("@limit_stock", product.LimitStock),
                new MySqlParameter("@supplier", product.Supplier),
                new MySqlParameter("@image", product.Image),
                new MySqlParameter("@produce_date", product.ProduceDate),
                new MySqlParameter("@quality_period", product.QualityPeriod),
                new MySqlParameter("@id", product.Id)
            };
            return MySqlDbHelper.Execute(sql, parameters);
        }

        /// <summary>
        /// 根据Id获取商品
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Product GetById(int id)
        {
            var sql = "select * from product where id=@id";
            return MySqlDbHelper.Query<Product>(sql, new MySqlParameter("@id", id));
        }

        /// <summary>
        /// 删除商品
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            var sql = "delete from product where id=@id";
            return MySqlDbHelper.Execute(sql, new MySqlParameter("@id", id));
        }

        /// <summary>
        /// 商品上架和下架
        /// </summary>
        /// <param name="id"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public int UpOrDown(int id, int status)
        {
            var sql = "update product set status=@status where id=@id;";
            return MySqlDbHelper.Execute(sql, 
                new MySqlParameter("@status", status),
                new MySqlParameter("@id", id));
        }
    }
}
